import os
import sys
import csv
import time
import MySQLdb
import requests
import json

environpassword = 'ONEBLOG_DATASOURCE_PASSWORD'
if environpassword not in os.environ.keys():
    print("environ:%s not config." % environpassword)
    sys.exit(1)

environak = "ONEBLOG_BAIDU_AK"
if environak not in os.environ.keys():
    print("environ:%s not config." % environak)
    sys.exit(1)

if (len(sys.argv) <= 1) or not os.path.exists(sys.argv[1]):
    print("Need import filename.", sys.argv)
    sys.exit(3)

print(os.environ[environpassword])
print(os.environ[environak])
print(sys.argv)

# 连接数据库
db = MySQLdb.connect("localhost", "root", os.environ[environpassword],
                     "covid19", charset='utf8')
cursor = db.cursor()

# 打开文件
importpath = os.path.abspath(sys.argv[1])
with open(importpath, encoding='GBK') as covid19file:
    reader = csv.reader(covid19file)
    try:
        for covidrow in reader:
            # print(covidrow)
            resturl = "https://api.map.baidu.com/geocoding/v3/?address=%s&output=json&ak=%s" % (
                covidrow[4], os.environ[environak])
            print(resturl)
            rsp = requests.get(resturl)
            if rsp.status_code == 200:
                rspJson = json.loads(rsp.text.encode())
            else:
                rspJson = {'status': 10}
            if (rspJson['status'] != 0):
                print(rspJson)
                continue
            casedate = covidrow[0] # time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            casecode = covidrow[1]
            sex = covidrow[2]
            age = int(covidrow[3])
            address = covidrow[4]
            longitude = rspJson['result']['location']['lng']
            latitude = rspJson['result']['location']['lat']
            sql = "INSERT INTO covidcase(casecode, casedate, sex, age, adress, longitude, latitude) VALUES ('%s', '%s','%s', %d, '%s', %f, %f )" % (
                casecode, casedate, sex, age, address, longitude, latitude)
            print(sql)
            cursor.execute(sql)
        db.commit()
    except:
        db.rollback()
    covid19file.close()


# 关闭数据库
print(cursor.execute("SELECT VERSION()"))
db.close()
